﻿CREATE PROCEDURE [dbo].[proc_Receipt_Getlist]
	(
		@CompanyId int,
		@SWId int,--发票状态
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int
	)
AS
Begin

declare @strSql NVARCHAR(4000)
set @strSql=''
if @CompanyId<>0
begin
	set @strSql=@strSql+' CompanyId='+Convert(varchar,@CompanyId)+' and'
end
if @SWId<>-1
begin 
	set @strSql=@strSql+' Flag='+Convert(varchar,@SWId)+' and'
end
if @Stext<>''
begin 
	set @strSql=@strSql+' CusName like ''%'+@Stext+'%'' and'
end

set @strSql='

	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,'''+@StartDate+' 00:00:00'')
	Set @eRq = Convert(Datetime,'''+@EndDate+' 23:59:59'')

	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY flag,RDate desc,LDate desc)AS Row,
	id,
		[CompanyId]
		,[CompanyName]
      ,[CusName]
      ,[OrderId]
      ,[NOE_Flag]
      ,[Je]
      ,[RDate]
      ,[Memo]
      ,[LDate]
      ,[LUserName]
      ,[LEName]
      ,[Flag]
      ,isnull([CancelDate],''1900-1-1'') as [CancelDate]
      ,[CancelUserName]
      ,[CancelEName]
		From Receipt 
		Where '+@strSql+'  RDate>=@sRq And RDate<=@eRq  
	)
	
	Select *,(select count(1) from list ) AS DataCount,
	 (select sum(Je) from list ) AS Je_Sum
	From list Where Row Between '+Convert(varchar,@StartIndex)+' and '+Convert(varchar,@EndIndex)+' Order By Row
'
exec(@strSql)
print(@strSql)
End
